package cn.net.autocode.platform.table.syncs;

import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import cn.net.autocode.dbManager.Dao;
import cn.net.autocode.dbManager.IdWorker;
import java.util.*;

public class SyncFromDb {

    private final Dao dictDao;

    public SyncFromDb(Dao dao){
        this.dictDao = dao;
    }

    public boolean execute(JSONArray tables){
        int len = tables.size();

        for(int i=0;i<len;i++){
            JSONObject tableModel = tables.getJSONObject(i);
            //处理表信息
            String tableName = tableModel.getString("tableName").toLowerCase();
            System.out.println(i+":"+tableName);
            JSONObject tableJson = dictDao.queryJSON("select * from ac_table where table_name=?",tableName);
            if(tableJson.isEmpty()){
                tableJson.put("id", IdWorker.ulid());
                tableJson.put("tableName",tableName.toLowerCase());
                tableJson.put("code", StrUtil.toCamelCase(tableName));
                tableJson.put("remarks",tableModel.getString("remarks"));
                tableJson.put("categoryCode","default");
                tableJson.put("isSync","N");
                dictDao.createJSON(tableJson,"ac_table");
            }

            String tableId = tableJson.getString("id");

            //处理列信息
            JSONArray columns = tableModel.getJSONArray("columns");
            int len1 = columns.size();
            List<String> fields = new ArrayList<>();
            for(int j=0;j<len1;j++){
                JSONObject columnModel = columns.getJSONObject(j);
                //columnName
                JSONObject fieldJson =  dictDao.queryJSON("select * from ac_table_fields where table_id=? and name=?",tableJson.getString("id"),columnModel.getString("name"));
                if(columnModel.getString("defaultvalue")!=null){
                    String dv = columnModel.getString("defaultvalue");
                    if(dv.startsWith("((")){
                        dv = dv.replace("((","").replace("))","");
                        columnModel.put("defaultvalue",dv);
                    }else if(dv.startsWith("(N'")){
                        dv = dv.replace("(N'","").replace("')","");
                        columnModel.put("defaultvalue",dv);
                    }else if(dv.startsWith("('")){
                        dv = dv.replace("('","").replace("')","");
                        columnModel.put("defaultvalue",dv);
                    }
                }
                fields.add(columnModel.getString("name"));
                if(fieldJson.isEmpty()){
                    columnModel.put("id", IdWorker.ulid());
                    columnModel.put("tableId",tableJson.getString("id"));
                    dictDao.createJSON(columnModel,"ac_table_fields");
                }else{
                    columnModel.put("id", fieldJson.getString("id"));
                    columnModel.put("tableId",tableJson.getString("id"));
                    if(fieldJson.getString("defaultvalue")!=null&&fieldJson.getString("defaultvalue").startsWith("@")){
                        columnModel.put("defaultvalue",fieldJson.getString("defaultvalue"));
                    }

                    String remarks = fieldJson.getString("remarks");
                    if(remarks!=null && !remarks.isBlank()){
                        columnModel.remove("remarks");
                    }
                    dictDao.updateJSON(columnModel,"ac_table_fields");
                }
            }
            //查询不存在的列
            //List<String> notExistsColumns = dictDao.queryListValue("select name from ac_table_fields where table_id=? and name not in (?)",String.class,tableId,fields);

            //删除不存在的列
            dictDao.executeUpdate("delete from ac_table_fields where table_id=? and name not in (?)",tableId,fields);

            //处理索引及约束
            JSONArray indexInfos = tableModel.getJSONArray("indexInfos");
            Map<String,JSONObject> indexMap = new HashMap<>();
            int len2 = indexInfos.size();
            //判断索引是否存在
            for(int j=0;j<len2;j++){
                JSONObject index = indexInfos.getJSONObject(j);
                index.put("name",index.getString("name").toLowerCase());
                String name = index.getString("name");
                if(indexMap.containsKey(name)){
                    JSONObject indexJson = indexMap.get(name);
                    String fieldName = indexJson.getString("fieldName")+","+index.getString("fieldName");
                    indexJson.put("fieldName",fieldName.toLowerCase());
                }else{
                    index.put("tableId",tableId);
                    index.put("fieldName",index.getString("fieldName").toLowerCase());
                    indexMap.put(name,index);
                }
            }

            //外键约束
            JSONArray  exportedKeys = tableModel.getJSONArray("exportedKeys");

            int len3 = exportedKeys.size();
            for(int j=0;j<len3;j++){
                JSONObject exportedKey = exportedKeys.getJSONObject(j);
                exportedKey.put("fkTable",exportedKey.getString("fkTable").toLowerCase());
                exportedKey.put("fkField",exportedKey.getString("fkField").toLowerCase());
                exportedKey.put("pkField",exportedKey.getString("pkField").toLowerCase());
                JSONObject refJson = dictDao.queryJSON("select * from ac_table_ref where table_id=? and fk_table=? and fk_field=?",tableId,exportedKey.getString("fkTable"),exportedKey.getString("fkField"));
                if(refJson.isEmpty()){
                    exportedKey.put("id",IdWorker.ulid());
                    exportedKey.put("tableId",tableId);
                    dictDao.createJSON(exportedKey,"ac_table_ref");
                }else{
                    exportedKey.put("id",refJson.getString("id"));
                    exportedKey.put("tableId",refJson.getString("tableId"));
                    dictDao.updateJSON(refJson,"ac_table_ref");
                }
            }
            dictDao.executeUpdate("update ac_table set is_sync='N' where id =? ",tableJson.getString("id"));
        }

        //删除不存在的表和列
        List<String> ids1 = dictDao.queryListValue("select table_id from ac_table_ref where " +
                "not exists(select 1 from ac_table_fields where ac_table_fields.table_id=ac_table_ref.table_id and ac_table_fields.name=ac_table_ref.pk_field)",String.class);

        List<String> ids2 = dictDao.queryListValue("select table_id from ac_table_ref where " +
                "not exists(select 1 from ac_table t1,ac_table_fields t2 where t1.id = t2.table_id and t1.table_name=ac_table_ref.fk_table AND ac_table_ref.fk_field = t2.name)",String.class);

        ids1.addAll(ids2);
        dictDao.executeUpdate("delete from ac_table_ref where " +
                "not exists(select 1 from ac_table_fields where ac_table_fields.table_id=ac_table_ref.table_id and ac_table_fields.name=ac_table_ref.pk_field)");

        dictDao.executeUpdate("delete from ac_table_ref where " +
                "not exists(select 1 from ac_table t1,ac_table_fields t2 where t1.id = t2.table_id and t1.table_name=ac_table_ref.fk_table AND ac_table_ref.fk_field = t2.name)");

        if(!ids1.isEmpty()){
            dictDao.executeUpdate("update ac_table set is_sync='N' where id in (?)",ids1);
        }
        return true;
    }


}
